package com.xhy;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args ) throws Exception {
        int test = 0;
        List test_jdbc;
        boolean flag =false;
//        例子执行成功
        if (test ==1) {
            test_jdbc = addEmp(4, "dd", "ff", 5, new Date(2001 - 05 - 01), 20.5, 5, 5);
            if (test_jdbc != null) {
                System.out.println("更新成功");
            } else System.out.println("执行失败");
        }else if (test ==2){
            flag = deleteEmp(4);
            if (flag){
                System.out.println("删除成功");
            }else System.out.println("删除失败");
        }else if (test == 3){
            flag = updateEmp("4","4",4,new Date(2000-02-05),4,4,4,4);
            if (flag){
                System.out.println("修改成功");
            }else System.out.println("修改失败");
        }else if (test ==0){
            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gz2105?uniCode=true&character=utf-8", "root", "123456");
            //3. 获取预编译对象并且发送SQL语句
            String sql = "select * from emp";
            PreparedStatement ps = conn.prepareStatement(sql);
            //4. 执行SQL语句并返回结果集
            ResultSet rs = ps.executeQuery();
            //5. 处理结果集
            while(rs.next()){//查看当前结果集中是否有下一个元素，如果没有返回false，如果有返回true
                System.out.print(rs.getInt("empno") + " ");
                System.out.print(rs.getString("ename")+ " ");
                System.out.print(rs.getString("job")+ " ");
                System.out.print(rs.getInt("mgr")+ " ");
                System.out.print(rs.getDate("hiredate")+ " ");
                System.out.print(rs.getDouble("sal")+ " ");
                System.out.print(rs.getInt("comm")+ " ");
                System.out.print(rs.getInt("deptno")+ " ");
                System.out.println();
            }
            //6. 关闭资源
            rs.close();
            ps.close();
            conn.close();
        }
    }
    //增加一条数据
    public static List<Object>  addEmp(int empno, String ename, String job, int mgr, Date hiredate, double sal, int comm, int deptno) throws Exception{
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gz2105?uniCode=true&character=utf-8", "root", "123456");
        String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,empno);
        ps.setString(2,ename);
        ps.setString(3,job);
        ps.setInt(4,mgr);
        ps.setDate(5, hiredate);
        ps.setDouble(6,sal);
        ps.setInt(7,comm);
        ps.setInt(8,deptno);
        ps.executeUpdate();
        List<Object> list = new ArrayList<>();
        list.add(ps);
        //关闭资源
        ps.close();
        conn.close();
        return list;
    }
    public static boolean deleteEmp(int empno) throws Exception {
        boolean flag = false;
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gz2105?uniCode=true&character=utf-8", "root", "123456");

        String sql = "delete from emp where empno = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,empno);
        int i = ps.executeUpdate();
        flag = i > 0 ? true : false;
        //关闭资源
        ps.close();
        conn.close();
        return flag;
    }
    public static boolean updateEmp(String ename, String job, int mgr, Date hiredate, double sal, int comm, int deptno,int empno) throws Exception{
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gz2105?uniCode=true&character=utf-8", "root", "123456");
        String sql = "update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,ename);
        ps.setString(2,job);
        ps.setInt(3,mgr);
        ps.setDate(4, hiredate);
        ps.setDouble(5,sal);
        ps.setInt(6,comm);
        ps.setInt(7,deptno);
        ps.setInt(8,empno);
        int i = ps.executeUpdate();
        boolean flag = i > 0 ? true : false;
        ps.close();
        conn.close();
        return flag;
    }
    public int dd(){
        return 0;
    }
}
